﻿CREATE PROCEDURE dbo.GetServiceManDetails_Opt2
@OID		bigint

AS 

---------------------------------------------------------------------------------------------------
--						Liberty
---------------------------------------------------------------------------------------------------
--  Stored Procedure :: GetServiceManDetails_Opt2
--                                                                            
---------------------------------------------------------------------------------------------------

SET NOCOUNT ON 

SELECT 	sm.OID,
		sm.Version		as 'ServiceManVersion',

		p.Version		as 'PersonVersion',
		p.FirstName,
		p.LastName,
		p.MiddleInitial,
		p.DOB,
		p.Sex,
		p.Phone1, 
		p.Phone2,
		p.eMailAddress, 

		sm.PrimaryAddressID,
		a1.Address1		as 'Primary Address1',
		a1.Address2		as 'Primary Address2',
		a1.City			as 'Primary City',
		a1.State		as 'Primary State',
		a1.PostalCode	as 'Primary PostalCode',
		a1.Country		as 'Primary Country',

		sm.PermanentAddressID,
		a2.Address1		as 'Permanent Address1',
		a2.Address2		as 'Permanent Address2',
		a2.City			as 'Permanent City',
		a2.State		as 'Permanent State',
		a2.PostalCode	as 'Permanent PostalCode',
		a2.Country		as 'Permanent Country',

		sh.OID			as 'ServiceHistoryOID',
		sh.Version		as 'ServiceHistoryVersion',
		sh.StartDate,
		sh.EndDate,
		sh.UnitAssignment,
		sh.TypeOfDischargeID,
		dis.Description as 'DischargeType',

		sh.BranchOfServiceID,
		br.Branch,

		sh.RankID,
		mr.PayGrade + ' / ' + mr.Abbreviation as 'Rank'

FROM dbo.ServiceMen sm
INNER JOIN dbo.People p
ON sm.OID = p.OID

LEFT OUTER JOIN dbo.ServiceHistory sh
ON sm.OID = SH.PersonnelID
AND sh.Version = (SELECT MAX(Version) FROM dbo.ServiceHistory
					WHERE PersonnelID = sm.OID)

INNER JOIN dbo.TypesOfMilitaryBranch br
ON sh.BranchOfServiceID = br.OID

INNER JOIN dbo.MilitaryRanks mr
ON sh.RankID = mr.OID

INNER JOIN dbo.TypesOfDischarge dis
ON sh.TypeOfDischargeID = dis.OID

LEFT OUTER JOIN dbo.Addresses a1
ON sm.PrimaryAddressID = a1.OID

LEFT OUTER JOIN dbo.Addresses a2
ON sm.PermanentAddressID = a2.OID

WHERE sm.OID = @OID


